

from db_conn.conn import conn

"""
从数据库获取条件筛选的数据
"""

def getCntByPYCG(province, year, category, grade):
    db = conn()
    cursor = db.cursor(prepared=True)

    # 执行sql语句
    sql = """SELECT COUNT(*) 
    FROM summary
    WHERE province=%s and year=%s and category=%s and grade=%s
    """
    cursor.execute(sql, (province,year,category,grade))
    # 获取数据
    res = cursor.fetchall()
    # print(res)
    # 关闭数据库
    db.close()
    res=res.pop()
    return res[0]
# """根据年份奖别获奖等级获取获奖个数，传入参数省份，输出list[（2022,'奖','等级',cnt）,]"""
# def getCntByP(province):
#     db = conn()
#     cursor = db.cursor(prepared=True)
#
#     # 执行sql语句
#     sql = """SELECT year,category,grade, COUNT(*) as prizecnt
# FROM summary
# WHERE province=%s
# GROUP BY year,category,grade;
# """
#     cursor.execute(sql,(province,))
#     # 获取数据
#     res = cursor.fetchall()
#
#     # 关闭数据库
#     db.close()
#     return res
def getByProvinceAndYear(province, year):
    db = conn()
    cursor = db.cursor(prepared=True)

    # 执行sql语句
    sql = "SELECT * FROM summary where province = %s and year= %s"
    cursor.execute(sql,(province,year))
    # 获取数据
    res = cursor.fetchall()

    # 关闭数据库
    db.close()
    return res

def getCntByPYC(province, year, category):
    db = conn()
    cursor = db.cursor(prepared=True)

    # 执行sql语句
    sql = "SELECT count(*) FROM summary where province = %s and year= %s and category=%s"
    cursor.execute(sql,(province,year,category))
    # 获取数据
    res = cursor.fetchall()
    # 关闭数据库
    db.close()
    res=res.pop()
    return res[0]

def getByProvince(province):
    db = conn()
    cursor = db.cursor()
    # 执行sql语句
    sql = "SELECT * FROM summary where province = '%s'" % (province)
    cursor.execute(sql)
    # 获取数据
    res = cursor.fetchall()
    # 关闭数据库
    db.close()
    return res
# def getByYear(year):
#     db = conn()
#     cursor = db.cursor()
#     # 执行sql语句
#     sql = "SELECT * FROM summary where year='%d'" % (year)
#     cursor.execute(sql)
#     # 获取数据
#     res = cursor.fetchall()
#     # 关闭数据库
#     db.close()
#     return res

if __name__ == '__main__':
    res=getCntByPYC('河北', 2022, "自然科学奖")
    print(res)

